﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;

namespace 酒店管理
{
    class database
    {
        private string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\Administrator\\Desktop\\预订接待.mdb";
        OleDbConnection Connect;
        OleDbDataAdapter da;

        public void DataSetInit(ref DataSet x)
            //用于程序初始化时把数据库中的数据读入DataSet
        {
            Connect = new OleDbConnection(connStr);
            da = new OleDbDataAdapter("SELECT * FROM 仓存",Connect);
            da.Fill(x,"仓存");
            da = new OleDbDataAdapter("SELECT * FROM 登记入住",Connect);
            da.Fill(x,"登记入住");
            da = new OleDbDataAdapter("SELECT * FROM 房间",Connect);
            da.Fill(x,"房间");
            da = new OleDbDataAdapter("SELECT * FROM 房间消费",Connect);
            da.Fill(x,"房间消费");
            da = new OleDbDataAdapter("SELECT * FROM 客户",Connect);
            da.Fill(x, "客户");
            da = new OleDbDataAdapter("SELECT * FROM 区域验证",Connect);
            da.Fill(x, "区域验证");
            da = new OleDbDataAdapter("SELECT * FROM 账单",Connect);
            da.Fill(x, "账单");
        }

        public void DataSetClear(ref DataSet x)
            //用于清空DataSet
        {
            x.Clear();
        }

        public void DatasetRefresh(ref DataSet x)
            //刷新程序DataSet
        {
            DataSetClear(ref x);
            DataSetInit(ref x);
        }
        
        private void execSql(string sqlstr,ref DataSet y)
        {
            //执行Sql语句操作
            Connect = new OleDbConnection(connStr);
            DatasetRefresh(ref y);
            Connect.Open();
            OleDbCommand sqlcmd = new OleDbCommand(sqlstr, Connect);
            try
            {
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                MessageBox.Show("数据库操作出错!");
            }
            Connect.Close();
            DatasetRefresh(ref y);
        }

        private void UpdateCMD(string sqlstr,ref DataSet y)
            //更新数据库的命令
        {
            execSql(sqlstr,ref y);
        }

        private void InsertCMD(string sqlstr, ref DataSet y)
        //对数据库进行数据插入操作的命令
        {
            execSql(sqlstr,ref y);
        }

        private void DeleteCMD(string sqlstr, ref DataSet y)
        //对数据库进行删除操作的命令
        {
            execSql(sqlstr,ref y);
        } 
        
        public void SelectCMD(string sqlstr, ref DataSet y)
            //在DataSet中创建一个Buffer表作为缓存
        {
            Connect = new OleDbConnection(connStr);
            DatasetRefresh(ref y);
            try
            {
                y.Tables["Buffer"].Clear();
            }
            catch (Exception)
            {
            }
            finally
            {
                da = new OleDbDataAdapter(sqlstr, Connect);
                da.Fill(y, "Buffer");
            }
        }
    }
}
